#!/usr/bin/env node

/**
 * 数据库连接测试脚本
 * 用于测试数据库连接和基本查询功能
 */

const mysql = require('mysql2/promise');

// 数据库连接配置
const dbConfig = {
    host: process.env.DB_HOST || '43.143.253.188',
    port: parseInt(process.env.DB_PORT) || 3306,
    user: process.env.DB_USER || 'nextjs',
    password: process.env.DB_PASSWORD || 'yEAiRE2jcxTyRBy5',
    database: process.env.DB_NAME || 'nextjs',
    charset: 'utf8mb4'
};

async function testConnection() {
    let connection = null;

    try {
        console.log('🔌 正在测试数据库连接...');
        console.log(`主机: ${dbConfig.host}:${dbConfig.port}`);
        console.log(`数据库: ${dbConfig.database}`);
        console.log(`用户: ${dbConfig.user}`);

        // 测试连接
        connection = await mysql.createConnection(dbConfig);
        console.log('✅ 数据库连接成功');

        // 测试基本查询
        console.log('\n📋 测试基本查询...');

        // 获取MySQL版本
        const [versionRows] = await connection.execute('SELECT VERSION() as version');
        console.log(`🔍 MySQL版本: ${versionRows[0].version}`);

        // 获取表数量
        const [tableCountRows] = await connection.execute(`
            SELECT COUNT(*) as count 
            FROM information_schema.TABLES 
            WHERE TABLE_SCHEMA = ?
        `, [dbConfig.database]);
        console.log(`📊 数据库表数量: ${tableCountRows[0].count}`);

        // 测试外键查询（兼容性测试）
        console.log('\n🔗 测试外键查询兼容性...');
        try {
            const [fkRows] = await connection.execute(`
                SELECT 
                    CONSTRAINT_NAME,
                    COLUMN_NAME,
                    REFERENCED_TABLE_NAME,
                    REFERENCED_COLUMN_NAME
                FROM information_schema.KEY_COLUMN_USAGE 
                WHERE TABLE_SCHEMA = ? 
                AND TABLE_NAME = 'users' 
                AND REFERENCED_TABLE_NAME IS NOT NULL
                LIMIT 1
            `, [dbConfig.database]);

            if (fkRows.length > 0) {
                console.log('✅ 外键查询成功（基本模式）');
                console.log(`   示例外键: ${fkRows[0].CONSTRAINT_NAME}`);
            } else {
                console.log('ℹ️ 没有找到外键关系');
            }
        } catch (fkError) {
            console.log('⚠️ 外键查询失败（基本模式）:', fkError.message);
        }

        // 测试高级外键查询
        try {
            const [fkAdvancedRows] = await connection.execute(`
                SELECT 
                    CONSTRAINT_NAME,
                    COLUMN_NAME,
                    REFERENCED_TABLE_NAME,
                    REFERENCED_COLUMN_NAME,
                    UPDATE_RULE,
                    DELETE_RULE
                FROM information_schema.KEY_COLUMN_USAGE 
                WHERE TABLE_SCHEMA = ? 
                AND TABLE_NAME = 'users' 
                AND REFERENCED_TABLE_NAME IS NOT NULL
                LIMIT 1
            `, [dbConfig.database]);

            if (fkAdvancedRows.length > 0) {
                console.log('✅ 外键查询成功（高级模式，包含规则）');
                console.log(`   示例外键: ${fkAdvancedRows[0].CONSTRAINT_NAME}`);
            } else {
                console.log('ℹ️ 没有找到外键关系');
            }
        } catch (fkAdvancedError) {
            console.log('⚠️ 外键查询失败（高级模式）:', fkAdvancedError.message);
            console.log('   这可能是MySQL版本兼容性问题，将使用基本模式');
        }

        // 测试表结构查询
        console.log('\n📝 测试表结构查询...');
        try {
            const [createRows] = await connection.execute('SHOW CREATE TABLE `users`');
            if (createRows.length > 0) {
                console.log('✅ 表结构查询成功');
                console.log(`   表名: users`);
            }
        } catch (createError) {
            console.log('⚠️ 表结构查询失败:', createError.message);
        }

        console.log('\n🎉 所有测试完成！');

    } catch (error) {
        console.error('❌ 测试失败:', error.message);
        process.exit(1);
    } finally {
        if (connection) {
            await connection.end();
            console.log('🔌 数据库连接已关闭');
        }
    }
}

// 运行测试
testConnection();
